package com.epoint.bookmanagement.utils;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.epoint.bookmanagement.bizlogic.bookinfo.domain.BookInfo;

import cn.hutool.core.util.ArrayUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;

/**
 * sql查询的工具类
 * 
 * @author
 *
 */
public class SQLUtil {

	/**
	 * 通过反射可模糊查询所有值字段
	 * @param pageIndex 页号
	 * @param pageSize 页面大小
	 * @param obj 要查询的对象信息
	 * @param tableName 要查询的表名称 
	 * @param fullClassName 对象的全路径名
	 * @param columns 要模糊查询的字段
	 * @return 结果
	 */
	public static List<Object> reflectionSelecTableInfo(Integer pageIndex, Integer pageSize, Object obj, String tableName, String fullClassName,
			String... columns) {

		Connection con = JDBCUtil.getConnection();
		
		PreparedStatement ps = null;
		ResultSet rs = null;

		List<Object> reslutList = new ArrayList<Object>();

		StringBuffer sql = new StringBuffer();
		sql.append(" select * from ").append(tableName).append(" where 1 =1 ");

		// 查看是否有模糊查询的字段
		int length = 0;
		if (!ArrayUtil.isEmpty(columns)) {
			length = columns.length;
		}
		try {
			Class<?> clazz = obj.getClass();
			Field[] fields = clazz.getDeclaredFields();
			for (Field field : fields) {
				field.setAccessible(true);
				Object value = field.get(obj);
				String name = field.getName().toLowerCase();
				
				// 如果属性不为空再添加sql
				if (ObjectUtil.isNotEmpty(value)) {
					
					// 判断是否需要模糊查询
					if (length > 0) {
						for (String column : columns) {
							if (StrUtil.equals(column, name)) {
								sql.append(" and ").append(name).append(" like concat('%', ? ,'%') ");
								length--;
								break;
							}
						}
					} else {
						sql.append(" and ").append(name).append(" = ? ");
					}

				}

			}
			// 判断是否需要分页
			if (pageIndex != null && pageSize != null) {
				sql.append(" limit ?,? ");
			}
			
			int i = 1;
			ps = con.prepareStatement(sql.toString());
			for (Field field : fields) {
				field.setAccessible(true);
				Object value = field.get(obj);
				// 如果属性不为空再设置值
				if (ObjectUtil.isNotEmpty(value)) {
					if (value instanceof String) {
						ps.setString(i, (String) value);
					} else if (value instanceof Integer) {
						ps.setInt(i, (Integer) value);
					} else if (value instanceof Date) {
						ps.setObject(i, value);
					}
					i++;
				}
			}
			if (pageIndex != null && pageSize != null) {
				ps.setInt(i++, pageIndex * pageSize);
				ps.setInt(i++, pageSize);
			}

			System.err.println("reflect -->sql-->" + ps);
			// 封装查询结果
			rs = ps.executeQuery();
			while (rs.next()) {
				Class<?> tmpObj = Class.forName(fullClassName);
				Object ni = tmpObj.newInstance();
				// 遍历属性并设置值
				for (Field field : fields) {
					String name = field.getName().toLowerCase();
					try {
						// 设置访问权限
						field.setAccessible(true);
						if (field.getType() == String.class) {
							field.set(ni, rs.getString(name));
						} else if (field.getType() == Integer.class) {
							field.set(ni, rs.getInt(name));
						} else if (field.getType() == Date.class) {
							field.set(ni, rs.getTimestamp(name));
						}
					} catch (IllegalAccessException e) {
						e.printStackTrace();
					}
				}
				System.out.println(ni);
				reslutList.add(ni);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.closeConnection(rs, ps, con);
		}
		return reslutList;
	}

}
